﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;

namespace ZLDC.DAL.Estate
{
                public class OprAllPropertyDAL
    {
                                                                                public static DataTable p_opr_AllProperty_list_table(int userid, int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            DataTable dt = null;
            dt = db.GetDataTable("p_opr_AllProperty_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            return dt;
        }

                                                        public static DataTable p_opr_AllProperty_detail_table(int Pty_id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Pty_id", Pty_id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_opr_AllProperty_detail", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                                public static void p_opr_AllProperty_save(int userid, string username, int pty_id, int roomid, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@username", username));
            parms.Add(new SqlParameter("@pty_id", pty_id));
            parms.Add(new SqlParameter("@roomid", roomid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_opr_AllProperty_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);
        }

                                                                                public static DataTable p_opr_AllProperty_list_system_table(int newbuildid, int oldbuildid, int typeid, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@newbuildid", newbuildid));
            parms.Add(new SqlParameter("@oldbuildid", oldbuildid));
            parms.Add(new SqlParameter("@typeid", typeid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_opr_AllProperty_list_system", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                public static DataTable NewSystemBuild_Auto(int top, string condition, _Database db)
        {
            DataTable dt = null;
            string strSql = "select top " + top + " id,name,eid,ename,finalName,usage from t_build " + condition;

            try
            {
                DataSet ds = db.Query(strSql);
                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }
            
            return dt;
        }

                                                                public static DataTable OldSystemBuild_Auto(int top, string condition, _Database db)
        {
            DataTable dt = null;
            string databaseip = ZLDC.Common.Estate._Utility.GetWebConfigSetting("OldDatabaseIP");
            string strSql = "select top " + top + " buildId as Id, buildName as name,ID as eid, name as ename, finalName,usage from " + databaseip + ".dbo.v_build " + condition;

            try
            {
                DataSet ds = db.Query(strSql);
                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                                public static DataTable NewSystemEstate_Auto(int top, string condition, _Database db)
        {
            DataTable dt = null;
            string strSql = "select top " + top + " id,name from t_estate " + condition;

            try
            {
                DataSet ds = db.Query(strSql);
                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                                public static DataTable OldSystemEstate_Auto(int top, string condition, _Database db)
        {
            DataTable dt = null;
            string databaseip = ZLDC.Common.Estate._Utility.GetWebConfigSetting("OldDatabaseIP");
            string strSql = "select top " + top + " id,name from " + databaseip + ".dbo.t_bi_Estate " + condition;

            try
            {
                DataSet ds = db.Query(strSql);
                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                                public static void p_opr_AllProperty_list_ok(int id, int typeid, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            parms.Add(new SqlParameter("@typeid", typeid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_opr_AllProperty_list_ok", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);
        }

                                                                public static DataTable p_build_lastlist_byeid_table(int eid, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@eid", eid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_build_lastlist_byeid", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                        public static DataTable BuildOldSystem(int eid, _Database db)
        {
            DataTable dt = null;
            string databaseip = ZLDC.Common.Estate._Utility.GetWebConfigSetting("OldDatabaseIP");
            string strSql = " select * from " + databaseip + ".dbo.t_bi_build where dltflag = 0 and e_id = " + eid +" order by name";

            try
            {
                DataSet ds = db.Query(strSql);
                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                        public static void p_housing_revoke(int bid, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@bid", bid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            db.execute_procedure("p_housing_revoke", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

        }

                                                        public static void p_opr_AllProperty_list_cancel(int eid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@eid", eid));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_opr_AllProperty_list_cancel", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

                                                                                                        public static DataTable p_opr_AllProperty_list2_table(int neweid, int oldeid, int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, ref int newcount, ref int oldcount, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@neweid", neweid));
            parms.Add(new SqlParameter("@oldeid", oldeid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pNewcount = new SqlParameter("@newcount", SqlDbType.Int, 4);
            pNewcount.Direction = ParameterDirection.Output;
            parms.Add(pNewcount);

            SqlParameter pOldcount = new SqlParameter("@oldcount", SqlDbType.Int, 4);
            pOldcount.Direction = ParameterDirection.Output;
            parms.Add(pOldcount);

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_opr_AllProperty_list2", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);
            newcount = (pNewcount.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewcount.Value);
            oldcount = (pOldcount.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pOldcount.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                        public static void p_GetBidByPtyID(int ptyID, out int newbid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@ptyID", ptyID));

            SqlParameter pNewbid = new SqlParameter("@newbid", SqlDbType.Int, 4);
            pNewbid.Direction = ParameterDirection.Output;
            parms.Add(pNewbid);

            db.execute_procedure("p_GetBidByPtyID", parms);

            newbid = (pNewbid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewbid.Value);

        }
    }
}
